<!--
    NAME: Daniel Yu
    DATE: 04/22/12
    CLASS: CS 143
    LAB: 1
    YEAR/TERM: Y3-T3
-->

<html>
    <head>
        <title>CS 143 - Project 1B</title>
    </head>
    <body>
        <b>SQL Query</b>
        <form action="query.php" method="POST">
            <textarea name="query" rows=10 cols=60></textarea>
            <br>
            <input type="submit" name="Submit">
        </form>
    </body>
</html>

<?php
    # Set DB globals here...
    define("USERNAME", "cs143");
    define("PASSWORD", "");
    define("HOST", "localhost");
    define("DB_NAME", "CS143");
    
    function runSQLCommand($query)
    {
        # Acquire the DB handle from the host using the login credentials 
        # provided
        
        # These values MUST be constants
        $db = mysql_connect(HOST, USERNAME, PASSWORD);
        
        # Select the specified database
        mysql_select_db(DB_NAME, $db);
        
        # Obtain the query results that were executed from the MySQL table
        $result = mysql_query($query) or die(mysql_error());
        
        echo "<table border=10>";
        
        # Obtain the number of attribute fields in the resulting tuples
        $fieldCount = mysql_num_fields($result);
        
        # Print the table headers
        for($cursor = 0; $cursor < $fieldCount; $cursor++)
        {
            # Acquire the table attribute name
            $fieldName = mysql_field_name($result, $cursor);
            
            echo "<th>";
            
            echo $fieldName;
            
            echo "</th>";
        }
        
        # Recurse through all tuples returned by the SQL query
        while($row = mysql_fetch_array($result))
        {
            echo "<tr>";
            
            # Obtain a count of all the fields in the tuple
            
            # Recurse through all the attributes in the tuple
            for($cursor = 0; $cursor < $fieldCount; $cursor++)
            {
                echo "<td>";
                
                echo $row[$cursor];
                
                echo "</td>";
            }
            
            echo "</tr>";
        }
        
        echo "</table>";
        
        # Close the SQL database connection
        mysql_close($db);
    }
    
    function main()
    {
        /*
        error_reporting(E_ALL);
        ini_set("display_errors", "1");
        */
        
        # Acquire the contents of the HTML POST field: "query"
        $query = $_POST["query"];
        
        # Check if a non-empty SQL statement has been provided
        if($query != "")
        {
            runSQLCommand($query);
        }
        
        return(0);
    }
    
    main();
?>
